/**
 * 
 */
package cn.edu.nju.ws.sview.wyge.relations.adataset.relations;

import java.lang.reflect.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import cn.edu.nju.ws.sview.cache.UserNameIDCache;
import cn.edu.nju.ws.sview.database.DBConnectionFactory;
import cn.edu.nju.ws.sview.wyge.relations.adataset.relations.datasetRelations.DatasetRelations;

/**
 * @author Weiyi Ge (geweiyi@gmail.com) 2012-8-6
 * 
 */
public class Relations {
	public static ArrayList<Relation> getRelationsByGroupId(int groupId){
		ArrayList<Relation> rels = new ArrayList<Relation>();
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement selps = conn
					.prepareStatement("SELECT relation_id FROM relation where group_id = ?");
			selps.setInt(1, groupId);
			ResultSet selrs = selps.executeQuery();
			while (selrs.next()) {
				int rel_id = selrs.getInt(1);
				rels.add(Relation.getInstance(rel_id));
			}
			selrs.close();
			selps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rels;
		
	}
	public static int getFavoriteNum(String ds_type, int ds_id, String user, String lang){
		int totalNum = 0;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement selps = conn
					.prepareStatement("SELECT count(relation_id) FROM re_ds_favor where ds_type = ? and ds_id = ? and user_id = ?");
			int user_id = UserNameIDCache.getInstance().getUserID(user);
			selps.setString(1, ds_type);
			selps.setInt(2, ds_id);
			selps.setInt(3, user_id);
			ResultSet selrs = selps.executeQuery();
			if(selrs.next()){
				totalNum = selrs.getInt(1);
			}
			selrs.close();
			selps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return totalNum;
	}
	public static ArrayList<Integer> getFavoriteIds(String ds_type, int ds_id,
			String user) {
		ArrayList<Integer> relIds = new ArrayList<Integer>();
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement selps = conn
					.prepareStatement("SELECT relation_id FROM re_ds_favor where ds_type = ? and ds_id = ? and user_id = ? ");
			int user_id = UserNameIDCache.getInstance().getUserID(user);
//			System.out.println(ds_type+"|"+ds_id+"|"+start+"|"+length);
			selps.setString(1, ds_type);
			selps.setInt(2, ds_id);
			selps.setInt(3, user_id);
			ResultSet selrs = selps.executeQuery();
			while (selrs.next()) {
				int rel_id = selrs.getInt(1);
//				if(rel.getLang().contentEquals(lang)){
					relIds.add(rel_id);
//				}
			}
			selrs.close();
			selps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return relIds;
	}
	public static ArrayList<Relation> getFavorite(String ds_type, int ds_id,
			String user, int start, int length, String lang) {
		ArrayList<Relation> rels = new ArrayList<Relation>();
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement selps = conn
					.prepareStatement("SELECT relation_id FROM re_ds_favor where ds_type = ? and ds_id = ? and user_id = ? limit ?, ?");
			int user_id = UserNameIDCache.getInstance().getUserID(user);
//			System.out.println(ds_type+"|"+ds_id+"|"+start+"|"+length);
			selps.setString(1, ds_type);
			selps.setInt(2, ds_id);
			selps.setInt(3, user_id);
			selps.setInt(4, start);
			selps.setInt(5, length);
			ResultSet selrs = selps.executeQuery();
			while (selrs.next()) {
				int rel_id = selrs.getInt(1);
				Relation rel = Relation.getInstance(rel_id);
				if(rel.getLang().contentEquals(lang)){
					rels.add(rel);
				}
			}
			selrs.close();
			selps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rels;
	}
	public static ArrayList<Relation> getFavorite(String ds_type, int ds_id,
			String user, int start, int length) {
		ArrayList<Relation> rels = new ArrayList<Relation>();
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement selps = conn
					.prepareStatement("SELECT relation_id FROM re_ds_favor where ds_type = ? and ds_id = ? and user_id = ? limit ?, ?");
			int user_id = UserNameIDCache.getInstance().getUserID(user);
			selps.setString(1, ds_type);
			selps.setInt(2, ds_id);
			selps.setInt(3, user_id);
			selps.setInt(4, start);
			selps.setInt(5, length);
			ResultSet selrs = selps.executeQuery();
			while (selrs.next()) {
				int rel_id = selrs.getInt(1);
				rels.add(Relation.getInstance(rel_id));
			}
			selrs.close();
			selps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rels;
	}

	public static void addFavorite(String ds_type, int ds_id, String user,
			int relation_id) {
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement insertps = conn
					.prepareStatement("insert ignore into re_ds_favor (relation_id, ds_type, ds_id, user_id) values(?, ?, ?, ?)");
			insertps.setInt(1, relation_id);
			insertps.setString(2, ds_type);
			insertps.setInt(3, ds_id);
			int user_id = UserNameIDCache.getInstance().getUserID(user);
			insertps.setInt(4, user_id);
			insertps.executeUpdate();
			insertps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void delFavorite(String ds_type, int ds_id, String user,
			int relation_id) {
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement delps = conn
					.prepareStatement("delete FROM re_ds_favor where relation_id = ? and ds_type = ? and ds_id = ? and user_id = ?");
			delps.setInt(1, relation_id);
			delps.setString(2, ds_type);
			delps.setInt(3, ds_id);
			int user_id = UserNameIDCache.getInstance().getUserID(user);
			delps.setInt(4, user_id);
			delps.executeUpdate();
			delps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/*public static ArrayList<Relation> getOriginal(String ds_type, int ds_id,
			String user, int start, int length, String lang) {
		DatasetRelations dr = new DatasetRelations(ds_type, ds_id, start, user, lang);
		return dr.getOriginals(length, lang);
	}*/

	public static int getOrdinaryNumForTmpDataset(ArrayList<Integer> docs,String user,int numLimit, String lang) {
		DatasetRelations dr = new DatasetRelations(docs, 0, user, lang);
		return dr.getRelationNumForTmpDataset(numLimit, lang);
//		int totalNum = 0;
//		DatasetRelations dr = new DatasetRelations(ds_type, ds_id, 0, user, lang);
//		totalNum = dr.getRelationNum(numLimit, lang);
//		return totalNum;
	}
	public static int getOrdinaryNum(String ds_type, int ds_id,String user,int numLimit, String lang) {
		DatasetRelations dr = new DatasetRelations(ds_type, ds_id, 0, user, lang);
		return dr.getAvailableRelationNum();
//		int totalNum = 0;
//		DatasetRelations dr = new DatasetRelations(ds_type, ds_id, 0, user, lang);
//		totalNum = dr.getRelationNum(numLimit, lang);
//		return totalNum;
	}
	public static ArrayList<Relation> getOrdinaryByPointer(String ds_type, int ds_id,
			String user, int start, int length, String lang) {
		DatasetRelations dr = new DatasetRelations(ds_type, ds_id, 0, user, lang);
		return dr.getRelationsByPointer(start, length, lang);
	}
	public static ArrayList<Relation> getOrdinary(String ds_type, int ds_id,
			String user, int relation_idstart, int length, String lang) {
		DatasetRelations dr = new DatasetRelations(ds_type, ds_id, relation_idstart, user, lang);
		return dr.getRelations(length, lang);
	}

	public static ArrayList<Relation> getOrdinaryBackward(ArrayList<Integer> docs,
			String user, int relation_idendbefore, int length, String lang) {
		DatasetRelations dr = new DatasetRelations(docs, relation_idendbefore, user, lang);
		return dr.getRelationsBackward(length);
	}
	
	public static ArrayList<Relation> getOrdinary(ArrayList<Integer> docs,
			String user, int relation_idstart, int length, String lang) {
		DatasetRelations dr = new DatasetRelations(docs, relation_idstart, user, lang);
		return dr.getRelations(length);
	}
	public static ArrayList<Relation> getOrdinary(String ds_type, int ds_id,
			String user, int relation_idstart, int length) {
		DatasetRelations dr = new DatasetRelations(ds_type, ds_id, relation_idstart, user);
		return dr.getRelations(length);
	}
	

	public static int getTrashNum(String ds_type, int ds_id, String user, String lang){
		int totalNum = 0;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement selps = conn
					.prepareStatement("SELECT count(relation_id) FROM re_ds_trash where ds_type = ? and ds_id = ? and user_id = ?");
			int user_id = UserNameIDCache.getInstance().getUserID(user);
			selps.setString(1, ds_type);
			selps.setInt(2, ds_id);
			selps.setInt(3, user_id);
			ResultSet selrs = selps.executeQuery();
			if(selrs.next()){
				totalNum = selrs.getInt(1);
			}
			selrs.close();
			selps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//		System.out.println("trashNum:"+totalNum);
		return totalNum;
	}
	
	public static ArrayList<Integer> getTrashIds(String ds_type, int ds_id, String user){
		ArrayList<Integer> relIds = new ArrayList<Integer>();
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement selps = conn
					.prepareStatement("SELECT relation_id FROM re_ds_trash where ds_type = ? and ds_id = ? and user_id = ?");
			int user_id = UserNameIDCache.getInstance().getUserID(user);
			selps.setString(1, ds_type);
			selps.setInt(2, ds_id);
			selps.setInt(3, user_id);
			ResultSet selrs = selps.executeQuery();
			while (selrs.next()) {
				int rel_id = selrs.getInt(1);
//				Relation rel = Relation.getInstance(rel_id);
//				if(rel.getLang().contentEquals(lang)){
					relIds.add(rel_id);
//				}
			}
			selrs.close();
			selps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return relIds;
	}
	public static ArrayList<Relation> getTrash(String ds_type, int ds_id,
			String user, int start, int length, String lang) {
		ArrayList<Relation> rels = new ArrayList<Relation>();
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement selps = conn
					.prepareStatement("SELECT relation_id FROM re_ds_trash where ds_type = ? and ds_id = ? and user_id = ? limit ?, ?");
			int user_id = UserNameIDCache.getInstance().getUserID(user);
			selps.setString(1, ds_type);
			selps.setInt(2, ds_id);
			selps.setInt(3, user_id);
			selps.setInt(4, start);
			selps.setInt(5, length);
			ResultSet selrs = selps.executeQuery();
			while (selrs.next()) {
				int rel_id = selrs.getInt(1);
				Relation rel = Relation.getInstance(rel_id);
				if(rel.getLang().contentEquals(lang)){
					rels.add(Relation.getInstance(rel_id));
				}
			}
			selrs.close();
			selps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rels;
	}

	public static ArrayList<Relation> getTrash(String ds_type, int ds_id,
			String user, int start, int length) {
		ArrayList<Relation> rels = new ArrayList<Relation>();
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement selps = conn
					.prepareStatement("SELECT relation_id FROM re_ds_trash where ds_type = ? and ds_id = ? and user_id = ? limit ?, ?");
			int user_id = UserNameIDCache.getInstance().getUserID(user);
			selps.setString(1, ds_type);
			selps.setInt(2, ds_id);
			selps.setInt(3, user_id);
			selps.setInt(4, start);
			selps.setInt(5, length);
			ResultSet selrs = selps.executeQuery();
			while (selrs.next()) {
				int rel_id = selrs.getInt(1);
				rels.add(Relation.getInstance(rel_id));
			}
			selrs.close();
			selps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rels;
	}

	public static void addTrash(String ds_type, int ds_id, String user,
			int relation_id) {
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement insertps = conn
					.prepareStatement("insert ignore into re_ds_trash (relation_id, ds_type, ds_id, user_id) values(?, ?, ?, ?)");
			insertps.setInt(1, relation_id);
			insertps.setString(2, ds_type);
			insertps.setInt(3, ds_id);
			int user_id = UserNameIDCache.getInstance().getUserID(user);
			insertps.setInt(4, user_id);
			insertps.executeUpdate();
			insertps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void delTrash(String ds_type, int ds_id, String user,
			int relation_id) {
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement delps = conn
					.prepareStatement("delete FROM re_ds_trash where relation_id = ? and ds_type = ? and ds_id = ? and user_id = ?");
			delps.setInt(1, relation_id);
			delps.setString(2, ds_type);
			delps.setInt(3, ds_id);
			int user_id = UserNameIDCache.getInstance().getUserID(user);
			delps.setInt(4, user_id);
			delps.executeUpdate();
			delps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static boolean isTrash(String ds_type, int ds_id, String user, int relation_id){
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement delps = conn
					.prepareStatement("select relation_id FROM re_ds_trash where relation_id = ? and ds_type = ? and ds_id = ? and user_id = ?");
			delps.setInt(1, relation_id);
			delps.setString(2, ds_type);
			delps.setInt(3, ds_id);
			int user_id = UserNameIDCache.getInstance().getUserID(user);
			delps.setInt(4, user_id);
			ResultSet rs = delps.executeQuery();
			if(rs.next()){
				return true;
			}
			delps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
}
